package com.kzw.test;

import java.util.List;

import org.hibernate.Session;
import org.junit.Test;

import com.kzw.entity.Course;
import com.kzw.entity.Score;
import com.kzw.entity.Student;
import com.kzw.util.HibernateUtil;

@SuppressWarnings("unchecked")
public class T1 {
	
	/**
	 * 查询"1"课程比"2"课程成绩高的学生的信息及课程分数
	 * */
	@Test
	public void test1() {
		Session session = HibernateUtil.getSession();
		
		String hql = "select s1.student, s1.course, s1.num, s2.course, s2.num  from Score s1, Score s2 where s1.student=s2.student and s1.course.id=1 and s2.course.id=2 and s1.num>s2.num";
		List<Object[]> list = session.createQuery(hql).list();
		for(Object[] objs : list) {
			Student s = (Student)objs[0];
			System.out.println("学生：" + s.getName());
			
			Course c1 = (Course)objs[1];
			System.out.println(c1.getName() + " 成绩：" + objs[2]);
			
			Course c2 = (Course)objs[3];
			System.out.println(c2.getName() + " 成绩：" + objs[4]);
			
			System.out.println("----------------");
		}
		
		HibernateUtil.closeSession();
	}
	
	/**
	 * 查询同时存在" 01 "课程和" 02 "课程的学生情况
	 * */
	@Test
	public void test2() {
		Session session = HibernateUtil.getSession();
		String hql = "from Score x left join fetch x.student left join fetch x.course  where x.course.id in (1, 2)";
		List<Score> list = session.createQuery(hql).list();
		for(Score s : list) {
			System.out.println(s.getStudent().getName() + ", " + s.getCourse().getName());
		}		
		
		HibernateUtil.closeSession();
	}
	
	/**
	 * 查询不存在" 01 "课程但存在" 02 "课程的情况
	 * */
	@Test
	public void test3() {
		Session session = HibernateUtil.getSession();
		String hql = "from Score where course.id=2";
		List<Score> list = session.createQuery(hql).list();
		for(Score s : list) {
			System.out.println(s.getStudent().getName() + ", " + s.getCourse().getName());
		}		
		
		HibernateUtil.closeSession();
	}
}
